package cn.zy.framework.helper;

import cn.zy.framework.util.PropsUtil;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @Author: Administrator
 * @Description:
 * @Date: Created in 17:38 2021/9/9
 * @Modified By:
 * @version: 1.0
 */
public final class DatabaseHelper {

	private static final Logger LOG = LoggerFactory.getLogger(cn.zy.framework.helper.DatabaseHelper.class);

	private static final ThreadLocal<Connection> CONNECTION_HOLDER;
	private static final QueryRunner QUERY_RUNNER;
	private static final BasicDataSource DATA_SOURCE;


	static {
		CONNECTION_HOLDER = new ThreadLocal<Connection>();
		QUERY_RUNNER = new QueryRunner();

		DATA_SOURCE = new BasicDataSource();
		DATA_SOURCE.setDriverClassName(ConfigHelper.getJdbcDriver());
		DATA_SOURCE.setUrl(ConfigHelper.getJdbcUrl());
		DATA_SOURCE.setUsername(ConfigHelper.getJdbcUsername());
		DATA_SOURCE.setPassword(ConfigHelper.getJdbcPassword());
	}

	/**
	 * 获取数据库连接
	 */
	public static Connection getConnection() {
		Connection connection = CONNECTION_HOLDER.get();
		if (connection == null) {
			try {
				connection = DATA_SOURCE.getConnection();
			} catch (SQLException e) {
				LOG.error("Error getting connection", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(connection);
			}
		}

		return connection;
	}

	/**
	 * 关闭数据库连接
	 */
	public static void closeConnection() {
		Connection conn = CONNECTION_HOLDER.get();
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				LOG.error("Error closing connection", e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}

	/**
	 * 查询实体列表
	 */
	public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {
		List<T> entityList;

		try {
			Connection conn = getConnection();
			entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);
		} catch (SQLException throwables) {
			LOG.error("query entity list failed", throwables);
			throw new RuntimeException(throwables);
		} finally {
			closeConnection();
		}
		return entityList;
	}

	/**
	 * 查询单个实体
	 */
	public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
		T entity;

		try {
			Connection conn = getConnection();
			entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
		} catch (SQLException throwables) {
			LOG.error("query entity list failed", throwables);
			throw new RuntimeException(throwables);
		} finally {
			closeConnection();
		}
		return entity;
	}

	/**
	 * 执行查询语句
	 */
	public static List<Map<String, Object>> executeQuery(String sql, Object... params) {
		List<Map<String, Object>> result;

		try {
			Connection conn = getConnection();
			result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
		} catch (SQLException e) {
			LOG.error("execute query failed", e);
			throw new RuntimeException(e);
		} finally {
			closeConnection();
		}
		return result;
	}

	/**
	 * 执行更新语句（包括update,insert,delete）
	 */
	public static int executeUpdate(String sql, Object... params) {
		int rows = 0;
		try {
			Connection conn = getConnection();
			rows = QUERY_RUNNER.update(conn, sql, params);
		} catch (SQLException e) {
			LOG.error("execute update failed", e);
			throw new RuntimeException(e);
		} finally {
			closeConnection();
		}
		return rows;
	}

	/**
	 * 插入实体
	 *
	 * @param entityClass
	 * @param fieldMap
	 * @param <T>
	 * @return
	 */
	public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> fieldMap) {
		if (MapUtils.isEmpty(fieldMap)) {
			LOG.error("can not insert entity: fieldMap is empty");
			return false;
		}
		String sql = "INSERT INTO " + getTableName(entityClass);
		StringBuilder columns = new StringBuilder("(");
		StringBuilder values = new StringBuilder("(");
		for (String fieldName : fieldMap.keySet()) {
			columns.append(fieldName).append(",");
			values.append("?,");
		}
		columns.replace(columns.lastIndexOf(","), columns.length(), ")");
		values.replace(values.lastIndexOf(","), values.length(), ")");
		sql += columns + " VALUES " + values;
		Object[] params = fieldMap.values().toArray();

		return executeUpdate(sql, params) == 1;
	}

	/**
	 * 更新实体
	 */
	public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> fieldMap) {
		if (MapUtils.isEmpty(fieldMap)) {
			LOG.error("can not update entity: fieldMap is empty");
			return false;
		}

		String sql = "UPDATE " + getTableName(entityClass) + " SET ";
		StringBuilder columns = new StringBuilder();
		for (String fieldName : fieldMap.keySet()) {
			columns.append(fieldName + "=?, ");
		}
		sql += columns.substring(0, columns.lastIndexOf(",")) + " WHERE id=?";
		List<Object> paramList = new ArrayList<Object>();
		paramList.addAll(fieldMap.values());
		paramList.add(id);
		Object[] params = paramList.toArray();

		return executeUpdate(sql, params) == 1;
	}

	/**
	 * 删除实体
	 */
	public static <T> boolean deleteEntity(Class<T> entityClass, long id) {
		String sql = "delete from " + getTableName(entityClass) + " WHERE id=?";
		return executeUpdate(sql, id) == 1;
	}

	/**
	 * 执行SQL文件
	 */
	public static void executeSqlFile(String filePath) {

		try (
				InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
				BufferedReader br = new BufferedReader(new InputStreamReader(in));
		) {
			String sql;
			while ((sql = br.readLine()) != null) {
				executeUpdate(sql);
			}
		} catch (Exception e) {
			LOG.error("execute sql file failed", e);
			throw new RuntimeException(e);
		}
	}

	private static String getTableName(Class<?> entityClass) {
		return entityClass.getSimpleName();
	}
}
